import json
import sqlite3
import os
from datetime import datetime
from typing import Dict, List, Any, Set

def load_json_from_txt(file_path: str) -> Any:
    """从txt文件加载JSON数据"""
    try:
        with open(file_path, 'r', encoding='utf-8') as f:
            return json.load(f)
    except FileNotFoundError:
        print(f"错误：文件 {file_path} 不存在")
        exit(1)
    except json.JSONDecodeError:
        print(f"错误：文件 {file_path} 格式无效")
        exit(1)

def determine_sqlite_type(value: Any) -> str:
    """根据Python值推断SQLite数据类型"""
    if value is None:
        return 'NULL'
    elif isinstance(value, bool):
        return 'INTEGER'  # SQLite没有布尔类型，用0/1表示
    elif isinstance(value, int):
        return 'INTEGER'
    elif isinstance(value, float):
        return 'REAL'
    elif isinstance(value, str):
        try:
            # 检查是否是日期字符串
            datetime.fromisoformat(value)
            return 'DATETIME'
        except (ValueError, TypeError):
            return 'TEXT'
    elif isinstance(value, (dict, list)):
        return 'TEXT'  # 复杂对象存储为JSON字符串
    else:
        return 'TEXT'

def analyze_fields(data: List[Dict]) -> Dict[str, Dict[str, Any]]:
    """分析所有字段，返回字段信息和是否为全null"""
    field_info = {}
    
    for item in data:
        for key, value in item.items():
            if key not in field_info:
                field_info[key] = {
                    'types': set(),
                    'null_count': 0,
                    'total_count': 0
                }
            
            field_info[key]['types'].add(determine_sqlite_type(value))
            field_info[key]['total_count'] += 1
            if value is None:
                field_info[key]['null_count'] += 1
    
    return field_info

def create_table_schema(data: List[Dict], table_name: str, conn: sqlite3.Connection) -> Dict[str, str]:
    """动态创建表结构并返回字段类型映射"""
    cursor = conn.cursor()
    
    # 分析所有字段
    field_info = analyze_fields(data)
    
    # 确定要创建的字段（跳过全为null的字段）
    field_types = {}
    for key, info in field_info.items():
        if info['null_count'] < info['total_count']:  # 不是全为null的字段
            # 确定最终类型（处理类型冲突）
            types = info['types'] - {'NULL'}  # 移除NULL类型
            if not types:  # 只有NULL类型（理论上不会发生，因为上面已经过滤）
                continue
            elif len(types) == 1:
                field_types[key] = types.pop()
            else:
                # 类型冲突处理
                if 'REAL' in types and 'INTEGER' in types:
                    field_types[key] = 'REAL'
                else:
                    field_types[key] = 'TEXT'  # 其他冲突都转为TEXT
    
    # 生成CREATE TABLE语句
    columns = [f'"{k}" {v}' for k, v in field_types.items()]
    create_table_sql = f'''
    CREATE TABLE IF NOT EXISTS "{table_name}" (
        id INTEGER PRIMARY KEY AUTOINCREMENT,
        {', '.join(columns)}
    )
    '''
    
    # 执行创建表
    cursor.execute(f'DROP TABLE IF EXISTS "{table_name}"')
    cursor.execute(create_table_sql)
    conn.commit()
    
    # 打印跳过的全null字段
    skipped_fields = [k for k, info in field_info.items() if info['null_count'] == info['total_count']]
    if skipped_fields:
        print(f"已跳过全为null的字段: {', '.join(skipped_fields)}")
    
    return field_types

def insert_data_to_sqlite(data: List[Dict], table_name: str, field_types: Dict[str, str], conn: sqlite3.Connection):
    """将数据插入到SQLite表中"""
    cursor = conn.cursor()
    
    for item in data:
        # 准备插入的数据（只包含field_types中存在的字段）
        columns = []
        values = []
        placeholders = []
        
        for key in field_types.keys():
            value = item.get(key)
            sql_type = field_types[key]
            
            # 根据SQL类型处理数据
            if value is None:
                processed_value = None
            elif sql_type == 'TEXT' and isinstance(value, (dict, list)):
                processed_value = json.dumps(value, ensure_ascii=False)
            elif sql_type == 'DATETIME' and isinstance(value, str):
                try:
                    # 尝试标准化日期格式
                    dt = datetime.fromisoformat(value)
                    processed_value = dt.strftime('%Y-%m-%d %H:%M:%S')
                except (ValueError, TypeError):
                    processed_value = value
            else:
                processed_value = value
            
            columns.append(f'"{key}"')
            values.append(processed_value)
            placeholders.append('?')
        
        # 构建并执行INSERT语句
        insert_sql = f'''
        INSERT INTO "{table_name}" ({', '.join(columns)})
        VALUES ({', '.join(placeholders)})
        '''
        cursor.execute(insert_sql, values)
    
    conn.commit()
    print(f"成功插入 {len(data)} 条记录到表 {table_name}")

def derive_table_name(input_file: str) -> str:
    """从输入文件名派生表名"""
    # 获取不带路径的文件名
    base_name = os.path.basename(input_file)
    # 去掉.txt后缀
    table_name = os.path.splitext(base_name)[0]
    # 替换可能存在的特殊字符为下划线
    table_name = ''.join(c if c.isalnum() else '_' for c in table_name)
    # 确保表名不以数字开头（SQLite限制）
    if table_name and table_name[0].isdigit():
        table_name = f'table_{table_name}'
    # 确保表名不为空
    if not table_name:
        table_name = 'json_data'
    return table_name

def main():
    # 配置参数
    input_file = "a.txt"  # JSON数据文件
    db_file = "json_data.db"    # SQLite数据库文件
    table_name = derive_table_name(input_file)  # 自动从文件名派生表名
    
    print(f"输入文件: {input_file}")
    print(f"使用表名: {table_name}")
    
    # 1. 从txt文件加载JSON数据
    print("从文件加载JSON数据...")
    json_data = load_json_from_txt(input_file)
    
    # 确保数据是列表形式
    if not isinstance(json_data, list):
        json_data = [json_data]
    
    # 2. 连接到SQLite数据库
    conn = sqlite3.connect(db_file)
    print(f"已连接到数据库 {db_file}")
    
    # 3. 动态创建表结构
    print("动态创建表结构...")
    field_types = create_table_schema(json_data, table_name, conn)
    
    # 4. 插入数据
    print("将数据插入数据库...")
    insert_data_to_sqlite(json_data, table_name, field_types, conn)
    
    # 5. 关闭连接
    conn.close()
    print("数据库操作完成")

if __name__ == "__main__":
    main()